package com.cxm.report.dao.provider;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

/**
 * @author scl.BlueOcean
 * @version 1.0
 * @date 2018年07月04 14:02
 * @description
 */
public class EfficiencyReportProvider {
    private final String TBL_REPORT = "t_efficiency_report";
    private final String TBL_ORDER = "order_v1";
    private final String TBL_ORDER_RESULT = "order_result_v1";

    public String batchInsert(String date){
        StringBuffer sql = new StringBuffer("insert into ");
        sql.append(TBL_REPORT).append("(orderNum,report_date,diffTimeScope)")
                .append("select count(1),date(o.create_date) report_date, ")
                .append("case when TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)/300=0 then 1 ")
                .append("when TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)/300<0 or TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)/300 is NULL then 0 ")
                .append("when TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)/300>6 then 7 ")
                .append("else ceil(TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)/300) end as diff ")
                .append("from ")
                .append(TBL_ORDER).append(" o left join ")
                .append(TBL_ORDER_RESULT).append(" r on r.order_no=o.order_no");
        if (StringUtils.hasText(date)) {
            sql.append(" where date(o.create_date)=date(#{date})");
        }
        sql.append(" GROUP BY diff,report_date");
        return sql.toString();
    }
    public String delete(String date){
        SQL sql = new SQL().DELETE_FROM(TBL_REPORT);
        if (StringUtils.hasText(date)) {
            sql.WHERE("date(report_date)=date(#{date})");
        }
        return sql.toString();
    }
}
